/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package util.www.jdbc;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Enumeration;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
import util.PropertiesUtil;
import util.www.sql.domain.BaseDatosSql;
import util.www.sql.domain.ColumnaSql;
import util.www.sql.domain.TablaSql;

    //http://stackoverflow.com/questions/5686514/jdbc-how-to-set-char-in-a-prepared-statement
    /*
    JDBC Type              Java Type
    -------------------------------------------
    CHAR                   String
    VARCHAR                String
    LONGVARCHAR            String
    NUMERIC                java.math.BigDecimal
    DECIMAL                java.math.BigDecimal
    BIT                    boolean
    BOOLEAN                boolean
    TINYINT                byte
    SMALLINT               short
    */


/**
 *
 * @author Rdelacruz
 */
public class JdbcUtil {
    
    //private static Map<String,Boolean> urlDrivers = new HashMap<String,Boolean>();
    
    public static void ejecutarTodo(Connection con, String sql, OperadorResulSet operador) throws SQLException {
        Statement st = null;
        try {                        
            st = con.createStatement();                        
            ResultSet rs = st.executeQuery(sql);            
            while(rs.next()) {
                operador.operar(con,rs);
            }
        }
        finally {
            if(st!=null) st.close();
        }
    }
    
    public static void ejecutarUnaVez(Connection con, String sql, OperadorResulSet operador) throws SQLException {
        Statement st = null;
        try {                        
            st = con.createStatement();                        
            ResultSet rs = st.executeQuery(sql);            
            if(rs.next()) {
                operador.operar(con,rs);
            }
        }
        finally {
            if(st!=null) st.close();
        }
    }

    //http://stackoverflow.com/questions/11699205/how-to-check-if-a-table-or-a-column-exists-in-a-database
    public static boolean existeTabla(Connection con, String nombreTabla) throws SQLException {
        DatabaseMetaData md = con.getMetaData();
        ResultSet rs = md.getTables(null, null, nombreTabla, null);
        return rs.next();       
    }
    
    //http://stackoverflow.com/questions/11699205/how-to-check-if-a-table-or-a-column-exists-in-a-database
    public static boolean existeColumna(Connection con, String nombreTabla, String columna) throws SQLException {
        DatabaseMetaData md = con.getMetaData();
        ResultSet rs = md.getColumns(null, null, nombreTabla, columna);
        return rs.next();
    }

    //Leer conexion JDBC de XML o archivo de propiedades
    //http://stackoverflow.com/questions/3674422/java-read-jdbc-connection-from-xml-file
    public static JdbcProperties getInstancia(String rutaJdbcProperties) throws IOException {
        Properties properties = PropertiesUtil.getProperties(rutaJdbcProperties);
        
        JdbcProperties jdbc = new JdbcProperties();
        jdbc.setUrl(properties.getProperty("jdbc.url"));
        jdbc.setDriver(properties.getProperty("jdbc.driver"));
        jdbc.setUsername(properties.getProperty("jdbc.username"));
        jdbc.setPassword(properties.getProperty("jdbc.password"));
        
        return jdbc;        
    }
    
    /*
    public static void desregistrarDriversCargadosUsados(Logger logger) {
        for(Map.Entry<String,Boolean> e : urlDrivers.entrySet()){
            String urlDriver = e.getKey();
            try {
                Driver driver = DriverManager.getDriver(urlDriver);
                if(driver!=null)
                    DriverManager.deregisterDriver(driver);
            }
            catch (SQLException ex) {
                
            }
        }
    }*/
    
    /**
     * Desregistra drivers cargados por el loader del objeto que llama a este metodo
     * @param logger 
     */
    //Para evitar el error "java.lang.OutOfMemoryError: PermGen space"
    //http://stackoverflow.com/questions/14200526/spring-tomcat-permgen-issue
    public static void desregistrarDriversCargados(Logger logger) {
        final Enumeration<Driver> drivers = DriverManager.getDrivers();
        while (drivers.hasMoreElements()) {
            final Driver driver = drivers.nextElement();
            try {
                DriverManager.deregisterDriver(driver);
                logger.log(Level.INFO, "Deregistered '{}' JDBC driver.", driver);
                System.out.println("Desregistrado: "+driver.getClass());
            } catch (SQLException e) {
                logger.log(Level.WARNING,"Failed to deregister '{}' JDBC driver.", driver);
                System.out.println("Fallo al desregistrar: "+driver.getClass());
            }
        }
    }
    
    public static int getMaximoNumeroConexiones(Connection con) throws Exception {
        DatabaseMetaData metaData = con.getMetaData();
        return metaData.getMaxConnections();
    }
    
    public static Connection getConexion(JdbcProperties jdbcProperties) throws Exception {
        Connection con = null;
        try {
            /*
            - Class.forName("MyClass") es cargado solo en la primera llamada y permanece en la memoria caché PermGen.
            - WebAppClassLoader verifica que MyClass ya esta cargado, entonces usa la instancia del caché
            */
            //http://stackoverflow.com/questions/10244553/will-using-class-forname-multiple-times-cause-memory-leak-running-inside-tomca
                                    
            String urlDriver = jdbcProperties.getDriver();
            Class.forName(urlDriver);  //carga el driver
            //if(urlDrivers.get(urlDriver)==null) {
            //    urlDrivers.put(urlDriver, Boolean.TRUE);
            //}
            
            //con = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databaseName=enaho;user=sa;password=12345;");            
            //con = DriverManager.getConnection(jdbcProperties.getUrlCompleta());
            con = DriverManager.getConnection(jdbcProperties.getUrl(), jdbcProperties.getUsername(), jdbcProperties.getPassword());
            
            //System.out.println("abriendo conexion");
        } catch (ClassNotFoundException ex) {
            //Logger.getLogger(ConexionBD.class.getName()).log(Level.SEVERE, null, ex);
            throw new Exception("Driver JDBC no encontrado",ex);
        } catch (SQLException ex) {
            //Logger.getLogger(ConexionBD.class.getName()).log(Level.SEVERE, null, ex);
            throw new Exception("No se pudo obtener una conexion a la Base de Datos",ex);
        }
        return con;
    }
    
    //DEFICIENTE: PORQUE REVISA TODAS LAS TABLAS
    private static void operarEnSQLServer(Connection con, BaseDatosSql bd) throws Exception {
        //http://stackoverflow.com/questions/2780284/how-to-get-all-table-names-from-a-database
        DatabaseMetaData md = con.getMetaData();
        ResultSet rs = md.getTables(null, null, "%", null);
        while (rs.next()) {
            //System.out.println(rs.getString(1)+"|"+rs.getString(2)+"|"+rs.getString(3));                

            //String nombreBD = rs.getString(1); 
            String esquema = rs.getString(2);                        
            if(esquema.equalsIgnoreCase("dbo")) {//dbo sale en SqlServer                        
                String nombreTabla = rs.getString(3);
                //System.out.println(nombreTabla);
                JdbcUtil.agregarTablaSiNoHaSidoAgregada(con,bd,nombreTabla);
                //bd.addTabla(t);//se hace en BasedatosUtil.getTabla()
            }
        }         
    }
    
    public static void agregarTablaSiExisteEnConexion(Connection con, BaseDatosSql bd, String esquema, String nombreTabla) throws Exception {
        DatabaseMetaData md = con.getMetaData();
        ResultSet rs = md.getTables(null, esquema,nombreTabla, null);        
        if(rs.next()) {
            String nombreTablaRs = rs.getString(3);
            JdbcUtil.agregarTablaSiNoHaSidoAgregada(con,bd,nombreTablaRs);
        }         
    }
    
    private static void agregarTablaSiNoHaSidoAgregada(Connection con, BaseDatosSql bd, String nombreTabla) throws Exception {
        
        if(!bd.existeTable(nombreTabla)) {
            
            Statement st = null;
            try {                
                TablaSql t = new TablaSql();
                //1.NOMBRE
                t.setNombre(nombreTabla);                          
                
                //2.COLUMNAS
                st = con.createStatement();
                ResultSet rs = st.executeQuery("select * from "+nombreTabla);                
                ResultSetMetaData rsmd = rs.getMetaData();                                
                
                int n = rsmd.getColumnCount();
                for(int i=1; i<=n; i++) {
                    ColumnaSql c =  new ColumnaSql();
                    
                    c.setNombre(rsmd.getColumnName(i));
                    c.setCodigoTipo(rsmd.getColumnType(i));
                    c.setTipo(rsmd.getColumnTypeName(i));
                    t.addColumna(c);
                }  
                
                //3.COLUMNAS CLAVE
                //http://tutorials.jenkov.com/jdbc/databasemetadata.html
                DatabaseMetaData dbmd = con.getMetaData();
                ResultSet rspk = dbmd.getPrimaryKeys(null, null, nombreTabla);                
                while(rspk.next()) {
                    String nombreColumnaPk = rspk.getString(4);
                    //System.out.println("PK="+nombreColumnaPk);
                    t.addColumnaClave(nombreColumnaPk);
                }                
                t.postProcesar();
                
                //AGREGANDO
                bd.addTabla(t);
            } finally {
                if(st!=null) st.close(); 
            }
        }
                
        //return bd.getTabla(nombreTabla);
    }
    
    //http://stackoverflow.com/questions/1915166/how-to-get-the-insert-id-in-jdbc
    //SOLO LLAMARLO UNA VEZ
    //Nota1: PreparedStatement extiende Statement
    //Nota2: PreparedStatement ps debe ser creado asi:
    //       ps = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);//SQL Server
    //       ps = con.prepareStatement(sql,new String[]{"id1","id2"}); //Oracle
    public static int getIdGeneradoInt(Statement st) throws SQLException {
        ResultSet rs = st.getGeneratedKeys();
        rs.next();
        return rs.getInt(1);
    }
    
    
    
    
    //http://stackoverflow.com/questions/19829123/how-get-length-or-size-of-resultset-in-java
    public static int getTamanhoResultSet(ResultSet rs) throws SQLException {
        //NOTA: Mas eficiente es usar: "select count(*) from miTabla"
        //http://stackoverflow.com/questions/192078/how-do-i-get-the-size-of-a-java-sql-resultset
        
        int size= 0;
        if (rs != null)   
        {  
          rs.beforeFirst();  
          rs.last();//Lee toda la tabla hasta llegar a la ultima fila. Por eso es menos eficiente  
          size = rs.getRow();  
        }  
        return size;
    }

    
}
